***********************************************************************************
* Merging National Employment Matrix with OEWS and O*NET data 
* last modified 7/16/2025
*
* Note: merge all data
*
***********************************************************************************


clear
capture log close 
set more off

***************
* Directories *
***************

cd "/Users/"

global dofile			=	"./Code/NEW-OEWS"
global matrix_data		=   "./Data/National Employment Matrix"
global oews_data		= 	"./Data/OEWS/all_excel"
global onet_data		= 	"./OccLink/ONET/ONET data/merged"
global results 			=	"./Results"
global temp				= 	"./Temp"


set scheme plotplainblind, permanently


*******************************************************************************
** Merge OEWS, NEM, and O*NET
*******************************************************************************
/*matrix*/
use "${temp}/matrix-ooh98-20combined.dta", replace
capture drop _merge
/* oews */
merge 1:1 soc year using "${temp}/oews98-21combined", keep(1 3) nogenerate
/* onet */
merge 1:1 soc year using "${temp}/onet_edu03-22combined", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_exp03-22combined", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_onsite_train03-22combined", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_job_train03-22combined", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_apprentice03-22combined", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_cert03-22combined", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_taskrating08-22combined.dta", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_wkcontext00-22combined.dta", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_skill00-22combined.dta", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_ability00-22combined.dta", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_interest00-22combined.dta", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_knowledge00-22combined.dta", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_work-activity00-22combined.dta", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_work-styles03-22combined.dta", keep(1 3) nogenerate
merge 1:1 soc year using "${temp}/onet_work-values00-22combined.dta", keep(1 3) nogenerate
/* job opening */
merge 1:1 soc year using "${temp}/jobopening00-20combined.dta", keep(1 3) nogenerate
save "${temp}/oews_matrix_ooh_onet98-20combined.dta", replace

export excel using "${temp}/oews_matrix_ooh_onet98-20combined.xlsx", firstrow(variables) replace
